from model.BaseModel import *
import datetime
import pandas as pd

class CommonDb(BaseModel):
    def __init__(self,tablename,dbname=""):
        if dbname == "":
            BaseModel.__init__(self)
        else:
            BaseModel.__init__(self,dbname)
        self.table = tablename

    def getTables(self):
        sql = "show tables;"
        rs = self.db.executeSql(sql)
        tables = []
        for cols in rs:
            tables.append(cols[0])
        return tables

    def getPartitionTables(self,table):
        sql = "SELECT PARTITION_NAME part, PARTITION_EXPRESSION expr, PARTITION_DESCRIPTION descr, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = '"+table+"';"
        rs = self.db.executeSql(sql)
        tables = []
        for cols in rs:
            tables.append(cols[0])
        return tables
    def executePartition(self):
        sql = "call proc_create_partition_all;"
        return self.db.executeSql(sql)

    def getCreateTableSql(self,tablename):
        sql = "show create table "+ tablename + ";"
        rs = self.db.executeSql(sql)
        return rs[0][1]

    def dropTable(self,tablename):
        try:
            sql = "drop table " + tablename + ";"
            print(sql)
            rs = self.db.executeSql(sql)
        except:
            pass

    def dropPartionTable(self,table_name,partition_name):
        try:
            sql = "ALTER TABLE "+table_name+" DROP PARTITION "+partition_name+";"
            print(sql)
            rs = self.db.executeSql(sql)
        except:
            pass

    def add(self,info,isctime=False,noUpdate=False):
        sInsertField = ""
        sInsertValue = ""

        if isctime == True:
            info["ctime"] = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")

        sUpdateSql = ""
        if len(info) > 0:
            for key in info.keys():
                sInsertField += "," + key
                sInsertValue += ",\"" + str(info[key]) + "\""
                sUpdateSql += key + "=\"" + str(info[key]) + "\","
            print(noUpdate == True)
            if noUpdate == False:
                sql = "insert into " + self.table + "(" + sInsertField[1:] + ") values(" + sInsertValue[1:]+ ") ON DUPLICATE KEY UPDATE " + sUpdateSql[:-1] + ";"
            else:
                sql = "insert into " + self.table + "(" + sInsertField[1:] + ") values(" + sInsertValue[1:] + ");"
        else:
            sql = "insert into " + self.table + " values();"
        #print(sql)
        return self.db.add(sql)

    def select(self,page,num,field,desc):
        if page <= 1:
            start = 0;
        else:
            start = (page-1) * num;
        sql = "select * from " + self.table + " order by " + field + " " + desc + " limit " + str(start) + "," + str(num) +  ";"
        print(sql)
        return self.db.executeSql(sql)

    def selectAll(self,wheresql,optionstr="*",map=False):
        sql = "select " + optionstr + " from " + self.table + " where 1 and " + wheresql + ";"
        print(sql)
        if map:
            return self.db.executeSqlMap(sql,self.table)
        else:
            return self.db.executeSql(sql)

    def selectByWhere(self,wheresql,page,num,field,desc):
        if page <= 1:
            start = 0
        else:
            start = (page-1) * num
        sql = "select * from " + self.table + " where 1 and " + wheresql + " order by " + field + " " + desc + " limit " + str(start) + "," + str(num) +  ";"
        print(sql)
        return self.db.executeSql(sql)

    def update(self,info,wheresql):
        sUpdateSql = ""
        for key in info.keys():
            sUpdateSql += key + "=\"" + str(info[key]) + "\","
        sql = "update " + self.table + " set " + sUpdateSql[0:-1] + " where 1 and " + wheresql;
        print(sql)
        return self.db.update(sql)

    def delete_where(self,wheresql):
        sql = "delete from " + self.table + " where 1 and " + wheresql + ";"
        print(sql)
        return self.db.update(sql)

    def executeMap(self,sql):
        print(sql)
        return self.db.executeSqlMap(sql,self.table)

    def executeByMap(self,sql,fields):
        print(sql)
        rs = self.db.executeSql(sql)
        newdata = []
        for row in rs:
            option = {}
            i = 0
            for field in fields:
                option[field] = row[0]
                i += 1
            newdata.append(option)
        return newdata

    def execute(self,sql):
        return self.db.executeSql(sql)

    def executeUpdate(self,sql):
        print(sql)
        return self.db.addOnly(sql)

    def getColumns(self):
        sql = "desc " + self.table + ";"
        rs = self.db.executeSql(sql)
        columns = []
        for cols in rs:
            columns.append(cols[0])
        return columns

    def getAllColumns(self,fields):
        sql = "show full columns from " + self.table + ";"
        print(sql)
        rs = self.db.executeSql(sql)
        columns = []
        print(fields)
        for cols in rs:
            if fields[0] == "*":
                columns.append({"field": cols[0], "type": cols[1], "comment": cols[8]})
            elif cols[0] in fields:
                columns.append({"field":cols[0],"type":cols[1],"comment":cols[8]})
        return columns

    def getAllFields(self):
        sql = "show full columns from " + self.table + ";"
        rs = self.db.executeSql(sql)
        columns = []
        for cols in rs:
            columns.append(cols[0])
        return columns

    def getMergeData(self,prefixTable,startDay,endDay,df=False):
        print(prefixTable)
        start = pd.to_datetime(startDay, format='%Y-%m-%d', errors='coerce')
        end = pd.to_datetime(endDay, format='%Y-%m-%d', errors='coerce')
        months = TimeUtil.getMonthRange(start,end)
        alldatas = []
        for month in months:
            if len(month) == 5:
                month = month[0:4]+"0"+month[4:]
            self.table = prefixTable + month
            print(self.table)
            data = self.selectAll("logdate between '"+startDay+"' and '"+endDay+"'","*",True)
            alldatas = alldatas + data
        if df == False:
            return alldatas
        else:
            return pd.DataFrame(alldatas, columns=self.getColumns())

    def selectFieldsByWhere(self,fields, wheresql,page,num,field,desc):
        if int(page) <= 1:
            start = 0
        else:
            start = (int(page)-1) * int(num)
        sql = "select " + fields + " from " + self.table + " where 1 and " + wheresql + " order by " + field + " " + desc + " limit " + str(start) + "," + str(num) +  ";"
        if fields == "*":
            fields = self.getAllFields()
            print(fields)
            return self.db.executeSqlMapByFields(sql, fields)
        else:
            return self.db.executeSqlMapByFields(sql, fields.split(","))

    def getAllData(self,fields,wheresql):
        sql = "select " + fields + " from " + self.table + " where 1 and " + wheresql + ";"
        if fields == "*":
            return self.db.executeSqlMap(sql, self.table)
        else:
            return self.db.executeSqlMapByFields(sql, fields.split(","))

    def selectRowDataByWhere(self,fields, wheresql):
        sql = "select " + fields + " from " + self.table + " where 1 and " + wheresql +  ";"
        return self.db.executeSqlMapByFields(sql,fields.split(","))

    def getPageData(self,selectstr,wherestr,page,num,orderfield,asc):
        sql = "select count(1) from " + self.table + " where 1 and " + wherestr
        rs = self.db.executeSql(sql)
        if rs != False and len(rs) > 0:
            cnt = rs[0][0]
            data = self.selectFieldsByWhere(selectstr,wherestr,page,num,orderfield,asc)
        else:
            cnt = 0
            data = []
        return {"code": 0, "msg": "获取数据成功", "count": cnt, "data": data}

    def getRowData(self,selectstr,wherestr):
        return self.selectRowDataByWhere(selectstr,wherestr)


